package com.mh.jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import com.mh.jdbc.entity.IPage;
import com.mh.jdbc.entity.MBean;
import com.mh.jdbc.entity.RowMapper;
import com.mh.jdbc.enums.LikeType;
import com.mh.jdbc.exception.DAOException;

public class JdbcDao {
	/**
	 * 查询集合
	 * @return
	 * @throws DAOException 
	 */
	public static List<Map<String,Object>> queryForListBySQL(String sql,Object...params) throws DAOException{
		List<Map<String,Object>> results = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			SqlUtils.setAttribute(ps, params);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取列数
			int cols = rsmd.getColumnCount();
			while(rs.next()){
				Map<String,Object> result = new HashMap<>();
				for(int i=1;i<=cols;i++) result.put(SqlUtils.T2B(rsmd.getColumnLabel(i)), rs.getObject(i));
				results.add(result);
			}
			return results;	
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,ps,conn);
		}
	}
	/**
	 * 查询对象集
	 * @param sql
	 * @param params
	 * @return
	 * @throws DAOException 
	 */
	public static <E> List<E> queryForListBySQL(RowMapper<E> mapper,String sql,Object...params) throws DAOException{
		List<E> result = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			SqlUtils.setAttribute(ps, params);
			rs = ps.executeQuery();
			int rownum = 0;
			while(rs.next()){
				result.add(mapper.mappingRow(rs, ++rownum));
			}
			return result;	
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,ps,conn);
		}
	}
	/**
	 * 查询单个对象
	 * @param sql
	 * @param params
	 * @return
	 * @throws DAOException 
	 */
	public static <E> E queryForObject(RowMapper<E> mapper,String sql,Object...params) throws DAOException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			SqlUtils.setAttribute(ps, params);
			rs = ps.executeQuery();
			int rownum = 0;
			return 	!rs.next() ?  null : mapper.mappingRow(rs, ++rownum);	
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,ps,conn);
		}
	}
	/**
	 * 增删改方法
	 * @param sql
	 * @param params
	 * @return
	 * @throws DAOException
	 */
	public static int executeUpdate(String sql,Object...params) throws DAOException{
		try {
			return executeUpdate(DBUtil.getConnection(),sql,params);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 增删改方法
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int executeUpdate(Connection conn,String sql,Object...params){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			SqlUtils.setAttribute(ps,params);
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally{
			DBUtil.colse(ps);
		}
	}
	/**
	 * 自动提交
	 * @param conn
	 * @param obj
	 * @return
	 */
	public static int insert(MBean mBean) throws DAOException{
		try {
			return insert(DBUtil.getConnection(),mBean);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 增加方法
	 * @param conn
	 * @return
	 * @throws DAOException
	 */
	public static int insert(Connection conn,MBean mBean) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.insertSQL(mBean));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 批量增加方法
	 * @param conn
	 * @return
	 * @throws DAOException
	 */
	public static int insert(List<MBean> mBeanList) throws DAOException{
		try {
			return DBUtil.getConnection().createStatement().executeUpdate(SqlUtils.insertBatchSQL(mBeanList));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件删除
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static int delete(MBean mBean) throws DAOException{
		try {
			return delete(DBUtil.getConnection(),mBean);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件删除
	 * @param conn
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static int delete(Connection conn,MBean mBean) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.deleteSQL(mBean));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 自定义条件删除
	 * @param conn
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static int delete(MBean mBean,String condite) throws DAOException{
		try {
			return delete(DBUtil.getConnection(),mBean,condite);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 自定义条件删除
	 * @param conn
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static int delete(Connection conn,MBean mBean,String condite) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.deleteSQL(mBean,condite));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件批量删除
	 * @param conn
	 * @param mBean
	 * @param ids
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int deleteBatch(MBean mBean,List<?> ids,String idName) throws DAOException{
		try {
			return deleteBatch(DBUtil.getConnection(),mBean,ids,idName);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件批量删除
	 * @param conn
	 * @param obj
	 * @param ids
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int deleteBatch(Connection conn, MBean mBean,List<?> ids,String idName) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.deleteBatchByIdsSQL(ids, mBean, idName));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件修改方法
	 * @param conn
	 * @param obj
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int update(MBean mBean,String idName) throws DAOException{
		try {
			return update(DBUtil.getConnection(),mBean,idName);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 单条件修改方法
	 * @param conn
	 * @param obj
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int update(Connection conn, MBean mBean,String idName) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.updateSQL(mBean, idName));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 多条件修改方法
	 * @param conn
	 * @param obj
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int update(MBean mBean,Map<String,?> condit) throws DAOException{
		try {
			return update(DBUtil.getConnection(),mBean,condit);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 多条件修改方法
	 * @param conn
	 * @param obj
	 * @param idName
	 * @return
	 * @throws DAOException
	 */
	public static int update(Connection conn,MBean mBean,Map<String,?> condit) throws DAOException{
		try {
			return conn.createStatement().executeUpdate(SqlUtils.updateSQL(mBean, condit));
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
	/**
	 * 查询结果集
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static List<Map<String,?>> queryForList(MBean mBean) throws DAOException{
		Map<String, Object> result = null;
		Connection conn = null;
		Statement st  = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(SqlUtils.querySQL(mBean));
			String[] colNames = SqlUtils.getColNames(mBean);
			int len = colNames.length;
			List<Map<String, ?>> results = new LinkedList<>();
			while(rs.next()){
				result = new HashMap<>();
				for(int i = 0;i<len;i++) result.put(colNames[i],rs.getObject(SqlUtils.B2T(colNames[i])));
				results.add(result);
			}
			return results;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
	}
	/**
	 * 查询对象结果集
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static List<MBean> queryForListEntity(MBean mBean) throws DAOException{
		Map<String, Object> result = null;
		Connection conn = null;
		Statement st  = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(SqlUtils.querySQL(mBean));
			String[] colNames = SqlUtils.getColNames(mBean);
			int len = colNames.length;
			List<MBean> results = new LinkedList<>();
			while(rs.next()){
				result = new HashMap<>();
				for(int i = 0;i<len;i++) result.put(colNames[i],rs.getObject(SqlUtils.B2T(colNames[i])));
				results.add(BeanUtil.map2Entity(result, mBean.getClass()));
			}
			return results;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
	}
	/**
	 * 查询单个对象
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static Map<String,?> queryForMap(MBean mBean) throws DAOException{
		Map<String, Object> result = new HashMap<>();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(SqlUtils.querySQL(mBean));
			if(rs.next()){
				String[] colNames = SqlUtils.getColNames(mBean);
				int len = colNames.length;
				for(int i = 0;i<len;i++) result.put(colNames[i] , rs.getObject(SqlUtils.B2T(colNames[i])));
			}
			return result;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
	}
	/**
	 * 查询单个对象
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static MBean queryForObject(MBean mBean) throws DAOException{
		Map<String, Object> result = new HashMap<>();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(SqlUtils.querySQL(mBean));
			if(rs.next()){
				String[] colNames = SqlUtils.getColNames(mBean);
				int len = colNames.length;
				for(int i = 0;i<len;i++) result.put(colNames[i] , rs.getObject(SqlUtils.B2T(colNames[i])));
			}
			return BeanUtil.map2Entity(result, mBean.getClass());
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
	}
	/**
	 * 查询某一字段
	 * @param obj
	 * @return
	 * @throws DAOException
	 */
	public static Object queryForObject(MBean mBean,String tergetPro) throws DAOException{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		Object result = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(SqlUtils.querySQL(mBean,tergetPro));
			if(rs.next()){
				result=rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
	}
	/**
	 * 分页查询
	 * @param <T>
	 * @param pg
	 * @return
	 * @throws DAOException
	 */
	public static <T> IPage<T> queryForPage(IPage<T> iPage) throws DAOException{
		MBean parmBean = iPage.getParmBean();
		Map<String, Object> result = null;
		List<MBean> results = new LinkedList<>();
		Connection conn = null;
		Statement st = null;
		ResultSet rsNum = null;
		ResultSet rsData = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rsNum = st.executeQuery(SqlUtils.getCountSQL(parmBean));
			if(rsNum.next())  iPage.setRows(rsNum.getInt("rows"));
			parmBean.setPage(iPage);
			rsData = st.executeQuery(SqlUtils.querySQL(parmBean));
			String[] colNames = SqlUtils.getColNames(parmBean);
			int len = colNames.length;
			while(rsData.next()){
				result = new HashMap<>();
				for(int i = 0;i<len;i++) result.put(colNames[i],rsData.getObject(SqlUtils.B2T(colNames[i])));
				MBean mBean = BeanUtil.map2Entity(result, parmBean.getClass());
				results.add(mBean);
			}
			iPage.setResult(results);
			return iPage;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rsNum,rsData,st,conn);
		}
	}
	/**
	 * 分页查询,左模糊条件
	 * @param <T>
	 * @param pg
	 * @return
	 * @throws DAOException
	 */
	public static <T> IPage<T> queryForPageLeftLikeWhere(IPage<T> iPage) throws DAOException{
		return queryForPageLikeWhere(iPage,LikeType.leftLink);
	}
	/**
	 * 分页查询,左模糊条件
	 * @param <T>
	 * @param pg
	 * @return
	 * @throws DAOException
	 */
	public static <T> IPage<T> queryForPageRighetLikeWhere(IPage<T> iPage) throws DAOException{
		return queryForPageLikeWhere(iPage,LikeType.rightLike);
	}
	/**
	 * 分页查询,全模糊条件
	 * @param <T>
	 * @param pg
	 * @return
	 * @throws DAOException
	 */
	public static <T> IPage<T> queryForPageFullLikeWhere(IPage<T> iPage) throws DAOException{
		return queryForPageLikeWhere(iPage,LikeType.bathLike);
	}
	/**
	 * 分页查询,模糊条件
	 * @param <T>
	 * @param pg
	 * @return
	 * @throws DAOException
	 */
	public static <T> IPage<T> queryForPageLikeWhere(IPage<T> iPage,int likeType) throws DAOException{
		MBean parmBean = iPage.getParmBean();
		String likePro = iPage.getLikePro();
		Map<String, Object> result = null;
		List<MBean> results = new LinkedList<>();
		Connection conn = null;
		Statement st = null;
		ResultSet rsNum = null;
		ResultSet rsData = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rsNum = st.executeQuery(SqlUtils.getCountSQL(parmBean,likePro,likeType));
			if(rsNum.next())  iPage.setRows(rsNum.getInt("rows"));
			parmBean.setPage(iPage);
			rsData = st.executeQuery(SqlUtils.querySQL(parmBean,likePro,likeType));
			String[] colNames = SqlUtils.getColNames(parmBean);
			int len = colNames.length;
			while(rsData.next()){
				result = new HashMap<>();
				for(int i = 0;i<len;i++) result.put(colNames[i],rsData.getObject(SqlUtils.B2T(colNames[i])));
				MBean mBean = BeanUtil.map2Entity(result, parmBean.getClass());
				results.add(mBean);
				
			}
			iPage.setResult(results);
			return iPage;
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rsNum,rsData,st,conn);
		}
	}
	/**
	 * 是否可以进行插入
	 * @param MBean
	 * @param proName
	 * @return boolean
	 * @throws DAOException
	 */
	public static boolean ifCanInsert(MBean mBean,String proName) throws DAOException{
		String sql = SqlUtils.getCountSQL(mBean,proName);
		if("0000".equals(sql)) return true;
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if(rs.next()) {
				if(rs.getInt("rows") > 0) return false;
			}
		} catch (SQLException e) {
			throw new DAOException();
		}finally {
			DBUtil.colse(rs,st,conn);
		}
		return true;
	}
}
